﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Microsoft.Crm.Sdk;
using System.Reflection;
using Microsoft.Crm.SdkTypeProxy;
using System.Collections;
using System.Reflection.Emit;
using System.Diagnostics;
using Microsoft.Win32;

namespace Streamsol.Crm.XrmDevelopmentToolkit
{
    public static class CrmSqlHelper
    {
        /// <summary>
        /// Adds the authentication logic for a SQL command in order to impersonate a CRM system-user.
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <param name="crmImpersonateUserId">A Guid of a CRM system-user of who will be impersonated.</param>
        /// <returns>A SqlCommand object with the updated command text and parameters.</returns>
        public static SqlCommand PrependCrmImpersonationChunk(this SqlCommand sqlCmd, Guid crmImpersonateUserId)
        {
            if (crmImpersonateUserId != Guid.Empty)
            {
                string context_info = @"DECLARE @binUserGuid varbinary(128)
                                    DECLARE @userGuid uniqueidentifier
                                    SELECT @userGuid = @ImpersonatedID
                                    SET @binUserGuid = CAST(@userGuid AS varbinary(128)) 
                                    SET context_info @binUserGuid
                                    ";
                sqlCmd.CommandText = context_info + sqlCmd.CommandText;

                SqlParameter impersonatedId = new SqlParameter("@ImpersonatedID", crmImpersonateUserId);
                sqlCmd.Parameters.Add(impersonatedId);
            }

            return sqlCmd;
        }

        /// <summary>
        /// Pre-appends the Command Text with the CRM authentication Query to impersonate a given user.
        /// </summary>
        /// <param name="command"></param>
        /// <param name="crmImpersonateUserId">The CRM systemuser Id to impersonate.</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(this SqlCommand command, Guid crmImpersonateUserId)
        {
            command = command.PrependCrmImpersonationChunk(crmImpersonateUserId);
            return command.ExecuteNonQuery();
        }

        /// <summary>
        /// Pre-appends the Command Text with the CRM authentication Query to impersonate a given user.
        /// </summary>
        /// <param name="command"></param>
        /// <param name="crmImpersonateUserId">The CRM systemuser Id to impersonate.</param>
        /// <returns></returns>
        public static object ExecuteScalar(this SqlCommand command, Guid crmImpersonateUserId)
        {
            command = command.PrependCrmImpersonationChunk(crmImpersonateUserId);
            return command.ExecuteScalar();
        }

        /// <summary>
        /// Pre-appends the Command Text with the CRM authentication Query to impersonate a given user.
        /// </summary>
        /// <param name="command"></param>
        /// <param name="crmImpersonateUserId">The CRM systemuser Id to impersonate.</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(this SqlCommand command, Guid crmImpersonateUserId)
        {
            return command.ExecuteReader(crmImpersonateUserId, CommandBehavior.Default);
        }

        /// <summary>
        /// Pre-appends the Command Text with the CRM authentication Query to impersonate a given user.
        /// </summary>
        /// <param name="command"></param>
        /// <param name="crmImpersonateUserId">The CRM systemuser Id to impersonate.</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(this SqlCommand command, Guid crmImpersonateUserId, CommandBehavior behaviour)
        {
            command = command.PrependCrmImpersonationChunk(crmImpersonateUserId);
            return command.ExecuteReader(behaviour);
        }

        /// <summary>
        /// Converts a IDataReader to a sequence of CRM entities, given the type.
        /// </summary>
        /// <typeparam name="TEntity">The type of BusinessEntity you wish to create.</typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static IEnumerable<TEntity> AsCrmEnumerable<TEntity>(this IDataReader reader)
            where TEntity : BusinessEntity, new()
        {
            //Get the properties
            Type entType = typeof(TEntity);
            PropertyInfo[] pInfo = entType.GetProperties();
            Dictionary<int, PropertyInfo> props = new Dictionary<int, PropertyInfo>();

            while (reader.Read())
            {
                //Get the PropertyInfo objects for each column that exists in our DataSource
                if (props.Count == 0)
                    props = (from p in pInfo
                             let a = reader.TryGetOrdinal(p.Name)
                             where a != -1
                             select new { a, p }).ToDictionary(n => n.a, n => n.p);

                //Create an instance of our type
                TEntity ret = new TEntity();

                foreach (var entry in props)
                {
                    int columnIndex = entry.Key;
                    PropertyInfo p = entry.Value;

                    //Get the value from the reader
                    object v = reader.GetValue(columnIndex);

                    if (!(v is DBNull) && v != null)
                    {
                        //get the property type 
                        Type propType = p.PropertyType;

                        if (propType.Name.Equals("String"))
                            p.SetValue(ret, v, null);
                        else
                        {
                            //Create a new instance of the property type if it's not a string 
                            // (ie a CrmType like CrmBoolean)
                            if (propType.Name.Equals("CrmDateTime"))
                                v = v.ToString();

                            object attribute;

                            if (propType.Name.Equals("Lookup") || propType.Name.Equals("Owner") || propType.Name.Equals("Customer"))
                            {
                                attribute = Activator.CreateInstance(propType, "", v);

                                #region Get the Entity Type Name
                                //try and get the entity type name
                                int typeColumnIndex = reader.TryGetOrdinal(p.Name + "type");

                                if (typeColumnIndex != -1)
                                {
                                    //get the value (int)
                                    object objCustomerTypeCode = reader.GetValue(typeColumnIndex);

                                    if (objCustomerTypeCode != null)
                                    {
                                        int customerTypeCode = -1;

                                        if (int.TryParse(objCustomerTypeCode.ToString(), out customerTypeCode))
                                        {
                                            if (customerTypeCode < 10000) // do nothing for custom entities
                                            {
                                                string customerType = EntityTypeCode.GetTypeName(customerTypeCode);

                                                PropertyInfo attTypeValue = propType.GetProperty("type");
                                                attTypeValue.SetValue(attribute, customerType, null);
                                            }
                                        }
                                    }
                                }
                                #endregion

                                #region Get the Entity name

                                //Check if we have the name field for the current property
                                //.. ie parentcustomeridname. If so, set it.
                                int nameColumnIndex = reader.TryGetOrdinal(p.Name + "name");
                                if (nameColumnIndex != -1)
                                {
                                    object va = reader.GetValue(nameColumnIndex);

                                    if (va is DBNull)
                                        va = null;

                                    PropertyInfo attTypeValue = propType.GetProperty("name");

                                    if (attTypeValue != null)
                                        attTypeValue.SetValue(attribute, va, null);
                                }

                                #endregion

                            }
                            else if (propType.Name.EndsWith("StateInfo"))
                            {
                                attribute = Activator.CreateInstance(propType);

                                //Get the inner property 'Value' and set it to the value from the datareader
                                PropertyInfo attValue = propType.GetProperty("Value");
                                attValue.SetValue(attribute, v, null);
                            }
                            else
                            {
                                attribute = Activator.CreateInstance(propType, v);
                            }

                            p.SetValue(ret, attribute, null);
                        }
                    }
                }
                yield return ret;
            }
        }

        /// <summary>
        /// Converts a IDataReader to a sequence of CRM entities, given the type.
        /// </summary>
        /// <typeparam name="TEntity">The type of BusinessEntity you wish to create.</typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static IEnumerable<TEntity> AsXrmEnumerable<TEntity>(this IDataReader reader)
            where TEntity : IXrmEntity, new()
        {
            //Get the properties
            Type entType = typeof(TEntity);
            PropertyInfo[] pInfo = entType.GetProperties();
            Dictionary<int, PropertyInfo> props = new Dictionary<int, PropertyInfo>();

            while (reader.Read())
            {
                //Get the PropertyInfo objects for each column that exists in our DataSource
                if (props.Count == 0)
                    props = (from p in pInfo
                             let a = reader.TryGetOrdinal(p.GetXrmFieldMappingAttribute().AttributeName)
                             where a != -1
                             select new { a, p }).ToDictionary(n => n.a, n => n.p);

                //Create an instance of our type
                TEntity ret = new TEntity();

                foreach (var entry in props)
                {
                    int columnIndex = entry.Key;
                    PropertyInfo p = entry.Value;

                    //Get the value from the reader
                    object v = reader.GetValue(columnIndex);

                    if (!(v is DBNull) && v != null)
                    {
                        //get the destination property type 
                        Type propType = p.PropertyType;

                        switch (propType.Name)
                        {
                            case "XrmReference":
                                string crmName = p.GetXrmFieldMappingAttribute().AttributeName;
                                int refName = reader.TryGetOrdinal(crmName + "name");
                                int refTypeName = reader.TryGetOrdinal(crmName + "type");
                                string referenceType = refTypeName >= 0 ? XrmReference.GetTypeName(reader.GetInt32(refTypeName)) : "";
                                string crmNameValue = refName >= 0 ? ConvertFromDbVal<string>(reader.GetValue(refName)) : null;
                                v = new XrmReference((Guid)v, referenceType, crmNameValue);
                                break;
                            case "XrmStatusInfo":
                                int crmStatusName = reader.TryGetOrdinal(p.GetXrmFieldMappingAttribute().AttributeName + "name");
                                string crmStatusNameValue = crmStatusName >= 0 ? ConvertFromDbVal<string>(reader.GetValue(crmStatusName)) : null;
                                v = new XrmStatusInfo(crmStatusNameValue, (int)v);
                                break;
                            case "XrmPicklistInfo":
                                int crmPicklistName = reader.TryGetOrdinal(p.GetXrmFieldMappingAttribute().AttributeName + "name");
                                string picklistNameValue = crmPicklistName >= 0 ? ConvertFromDbVal<string>(reader.GetValue(crmPicklistName)) : null;
                                v = new XrmPicklistInfo(picklistNameValue, (int)v);
                                break;
                            case "String":
                                //check for a name field. in the case a statecode field should be a string.
                                int nameField = reader.TryGetOrdinal(p.GetXrmFieldMappingAttribute().AttributeName + "name");
                                v = nameField >= 0 ? ConvertFromDbVal<string>(reader.GetValue(nameField)) : null;
                                break;
                            case "Single":
                                if (v is double)
                                    v = (Single)(Double)v;
                                break;
                        }

                        p.SetValue(ret, v, null);

                    }
                }

                yield return ret;
            }
        }

        internal static XrmFieldMappingAttribute GetXrmFieldMappingAttribute(this MemberInfo mInfo)
        {
            return ((XrmFieldMappingAttribute)mInfo
                   .GetCustomAttributes(typeof(XrmFieldMappingAttribute), true).First());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="record"></param>
        /// <param name="name">The name of the field to try and find</param>
        /// <returns>The index of the found field, -1 if not found.</returns>
        public static int TryGetOrdinal(this IDataRecord record, string name)
        {
            int index = -1;
            try { index = record.GetOrdinal(name); }
            catch (IndexOutOfRangeException) {/*do nothing*/}
            return index;
        }

        public static IEnumerable<T> MapObject<T>(this IDataReader dr, Func<IDataReader, T> convertFunction)
        {
            while (dr.Read())
            {
                yield return convertFunction(dr);
            }
        }

        public static T ConvertFromDbVal<T>(object obj)
        {
            if (obj == null || obj == DBNull.Value)
            {
                return default(T);
            }
            else
            {
                return (T)obj;
            }
        }
    }
}
